package org.yoao.crm.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.crm.bean.Role;
import org.yoao.crm.bean.StaticBean;
import org.yoao.crm.tools.DaoUtil;

/**
 * @author yxwonline.com
 * @description
 * @date 2010-7-31 下午01:31:36
 */
public class RoleDAO extends DBManager {
	private static final Logger log = Logger.getLogger(RoleDAO.class);

	private Role findSingle(Map data) throws Exception {
		Role role = null;
		if (data != null && !data.isEmpty()) {
			role = new Role();
			role.setId(Integer.parseInt(data.get("ID").toString()));
			role.setRoleId((String) data.get("ROLEID"));
			role.setParentId((String) data.get("PARENTID"));
			role.setRoleName((String) data.get("ROLENAME"));
			role.setActionPermission((String) data.get("ACTIONPERMISSION"));
		}
		return role;
	}

	public Role findRoleByRoleId(String roleId) throws Exception {
		String sql = "select * from userRole where roleId='" + roleId + "'";
		return findSingle(querySingleRow(sql));
	}

	public Role findById(String id) throws Exception {
		String sql = "select * from userRole where id=" + id;
		return findSingle(querySingleRow(sql));
	}

	public Role findParentByRoleId(String roleId) throws Exception {
		String sql = "select u.* from userRole as u where u.roleId="
				+ "(select u2.parentId from userRole as u2 where u2.roleId='"
				+ roleId + "')";
		return findSingle(querySingleRow(sql));
	}

	public Role findChildById(String id) throws Exception {
		String sql = "select u.roleId from userRole as u where u.parentId=("
				+ "select u2.roleId from userRole as u2 where u2.id=" + id
				+ ")";
		return findSingle(querySingleRow(sql));
	}

	public int getDataCount(Map map) throws Exception {
		String sql = "select count(id) as userCount from userRole";
		String where = " where ";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + key + "='" + value + "' and ";
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(" where "))
			where = "";
		sql += where;
		String result = querySingleValue(sql);
		if (result == null || result.trim().equals(""))
			result = "0";
		return Integer.parseInt(result);
	}

	public List findAll() throws Exception {
		String sql = "select ID, ROLEID, PARENTID, ROLENAME from userRole";
		return find(sql);
	}

	/**
	 * @author yxwonline.com
	 * @description 递归获取到当前权限的所有子权限
	 * @date 2010-8-3 上午01:32:38
	 */
	public List findAllChilds(String currRoleId) throws Exception {
		List result = findAllChildRole(currRoleId);
		if (result == null)
			result = new ArrayList();
		String sql = "select roleId, roleName from userRole where roleId='"
				+ currRoleId + "'";
		Map map = querySingleRow(sql);
		if (map != null && !map.isEmpty())
			result.add(map);
		return result;
	}

	private List findAllChildRole(String currRoleId) throws Exception {
		String sql = " from userRole ";
		if (currRoleId != null && !currRoleId.trim().equals("")) {
			sql = sql + " where parentId='" + currRoleId + "'";
		}
		String[] columnNameArray = { "ROLEID", "ROLENAME" };
		List list = queryAssignColumns(sql, columnNameArray);
		if (list != null && !list.isEmpty()) {
			int size = list.size();
			for (int i = 0; i < size; i++) {
				Map map = (Map) list.get(i);
				String roleId = map.get("ROLEID").toString();
				List child = findAllChildRole(roleId);
				if (child != null && !child.isEmpty()) {
					for (int k = 0; k < child.size(); k++) {
						list.add(child.get(k));
					}
				}
			}
			return list;
		} else
			return null;
	}

	public String findAllParentID(String currRoleId) throws Exception {
		String sql = "select parentId from userRole where roleId='"
				+ currRoleId + "'";
		StringBuffer parentId = new StringBuffer(querySingleValue(sql));
		if (!parentId.toString().trim().equals(StaticBean.ROLE_SYSTEM))
			return parentId.append(";" + findAllParentID(parentId.toString()))
					.toString();
		else
			return parentId.toString();
	}

	/**
	 * @author yxwonline.com
	 * @description 判断是否有子角色
	 * @date 2010-8-25 下午07:34:08
	 */
	public List findChildByParentID(String parentId) throws Exception {
		String sql = "select roleId from userRole where parentId = '"
				+ parentId + "'";
		List list = querySingleColumn(sql);
		if (list == null || list.isEmpty())
			return null;
		else
			return list;
	}

	public List find(int startRow, int endRow, Map map) throws Exception {
		String sql = "select top " + endRow
				+ " u.id, u.roleId, ur.roleName as parentId, "
				+ "u.roleName, u.actionPermission from userRole as u "
				+ "left join userRole as ur on ur.roleId = u.parentId "
				+ "where u.id not in(";
		String filterSql = "select top " + startRow
				+ " r2.id from userRole as r2 ";

		String where = "";
		String fwhere = "";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + "u." + key + "='" + value + "' and ";
				fwhere = fwhere + "r2." + key + "='" + value + "' and ";
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(""))
			where = null;
		if (fwhere.endsWith(" and "))
			fwhere = fwhere.substring(0, fwhere.length() - 4);
		if (fwhere.equals(""))
			fwhere = null;

		if (where == null) {
			sql = sql + filterSql + ")";
		} else {
			filterSql = filterSql + " where " + fwhere + " ";
			sql = sql + filterSql + ") and " + where;
		}
		return find(sql);
	}

	private List find(String sql) throws Exception {
		List list = queryMultiColumn(sql);
		List result = null;
		if (list != null && !list.isEmpty()) {
			result = new ArrayList();
			for (int i = 0; i < list.size(); i++) {
				Map map = (Map) list.get(i);
				Role role = findSingle(map);
				if (role == null)
					role = new Role();
				result.add(role);
			}
		}
		return result;
	}

	public int insert(Role role) throws Exception {
		String sql = "insert into userRole(roleId, parentId, roleName, actionPermission)values('"
				+ role.getRoleId()
				+ "', '"
				+ role.getParentId()
				+ "', '"
				+ role.getRoleName()
				+ "', '"
				+ role.getActionPermission()
				+ "')";
		int result = -1;
		if (update(sql) > -1) {
			sql = "select id from userRole where roleId='" + role.getRoleId()
					+ "'";
			String r = querySingleValue(sql);
			if (r != null && !r.trim().equals("")) {
				result = Integer.parseInt(r);
			}
		}
		return result;
	}

	public int modify(Role role) throws Exception {
		String sql = "update userRole set roleName='" + role.getRoleName()
				+ "', parentId='" + role.getParentId() + "',actionPermission="
				+ DaoUtil.setValue(role.getActionPermission())
				+ " where roleId='" + role.getRoleId() + "'";
		return update(sql);
	}

	public int delete(String id) throws Exception {
		String sql = "delete from userRole where roleId<>'"
				+ StaticBean.ROLE_ADMIN + "' and roleId<>'"
				+ StaticBean.ROLE_HQ + "' and id=" + id;
		return update(sql);
	}

	public synchronized String create() {
		try {
			log.info("开始初始化表userRole...");
			String sql = "SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[userRole]');";
			List list = queryMultiColumn(sql);
			if (list == null) {
				log
						.warn("系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。");
				return "系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。";
			}
			if (list.isEmpty()) {
				log.info("表userRole不存在，开始创建...");
				sql = "/****** 对象:  Table [dbo].[userRole]    脚本日期: 06/24/2009 17:40:16 ******/ "
						+ "SET ANSI_NULLS ON;"
						+ "SET QUOTED_IDENTIFIER ON;"
						+ "SET ANSI_PADDING ON;"
						+ "CREATE TABLE [dbo].[userRole]("
						+ "[id] [int] IDENTITY(1,1) NOT NULL,"
						+ "[roleId] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,"
						+ "[parentId] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,"
						+ "[roleName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,"
						+ "CONSTRAINT [PK_userRole] PRIMARY KEY CLUSTERED"
						+ "("
						+ "[id] ASC"
						+ ")WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]"
						+ ") ON [PRIMARY];" + "SET ANSI_PADDING OFF;";

				if (this.update(sql) < 0) {
					log.warn("表userRole初始化失败!");
					return "表userRole初始化失败!";
				}
				log.info("表userRole创建成功!");
				log.info("给表userRole初始化数据!");
				sql = "insert into userRole(roleId, parentId, roleName) values('"
						+ StaticBean.ROLE_ADMIN
						+ "', '"
						+ StaticBean.ROLE_SYSTEM
						+ "',  '系统管理员');"
						+ "insert into userRole(roleId, parentId, roleName) values('"
						+ StaticBean.ROLE_HQ
						+ "',    '"
						+ StaticBean.ROLE_ADMIN
						+ "',   '总公司');"
						+ "insert into userRole(roleId, parentId, roleName) values('"
						+ StaticBean.ROLE_BH
						+ "',    '"
						+ StaticBean.ROLE_HQ
						+ "',      '分公司');"
						+ "insert into userRole(roleId, parentId, roleName) values('"
						+ StaticBean.ROLE_USER
						+ "',  '"
						+ StaticBean.ROLE_BH
						+ "',      '操作人员')";
				update(sql);
				return "表userRole创建成功!";
			} else {
				log.info("表userRole已存在!");
				return "表userRole已存在!";
			}
		} catch (Exception e) {
			log.warn("表userRole初始化失败!");
			e.printStackTrace();
			return "表userRole初始化失败!" + e.toString();
		}
	}
}